package com.yenlien.traceyun.plant.dao;

import com.alibaba.fastjson.JSONObject;
import com.yenlien.traceyun.plant.entity.Farm;
import com.yenlien.traceyun.webase.dao.BaseJpaDao;
import org.springframework.data.jpa.repository.Query;

import java.util.List;

public interface FarmDao extends BaseJpaDao<Farm> {


    @Query(value = "SELECT tc.credit_code, tpf.type farming_type, tpf.begin_time start_time, tpf.end_time FROM t_plant_farm tpf " +
            "LEFT JOIN t_company tc ON tpf.company_id = tc.id WHERE tc.parent_id IN (?1) AND tc.dock_province = '1'", nativeQuery = true)
    List<JSONObject> dock3(List<String> parentIdList);

    @Query(value = "SELECT tpf.type farming_type, tpf.begin_time start_time, tpf.end_time FROM t_plant_farm tpf " +
            "WHERE tpf.company_id = ?1", nativeQuery = true)
    List<JSONObject> dock3(String companyId);

    Farm findById(String id);


    @Query(value = "SELECT * FROM t_plant_farm WHERE alarm_flag = true OR notice_flag = true", nativeQuery = true)
    List<Farm> findByAlarm();

    @Query(value = "SELECT type FROM t_plant_farm WHERE company_id = ?1 GROUP BY type", nativeQuery = true)
    List<String> findType(String companyId);

    @Query(value = "SELECT principal_name FROM t_plant_farm WHERE company_id = ?1 GROUP BY principal_name", nativeQuery = true)
    List<String> findPrincipalName(String companyId);

    List<Farm> findByUserId(String userId);

    @Query(value = "select DATE_FORMAT(begin_time,'%Y') from t_plant_farm order by createtime desc limit 0,1", nativeQuery = true)
    String findBeginYear(String plantId);

    @Query(value = "select DATE_FORMAT(begin_time,'%Y') from t_plant_farm where plant_id = ?1 order by begin_time desc limit 0,1", nativeQuery = true)
    String findEndYear(String id);

    @Query(value = "select * from t_plant_farm where plant_id = ?1 and YEAR(begin_time)= ?2", nativeQuery = true)
    List<Farm> findByPlantIdAndYear(String id, int year);

    /**
     * 管理员查询
     *
     * @param plantId
     * @return
     */
    @Query(value = "select * from t_plant_farm where batch = ?1 and status!=2 order by begin_time asc", nativeQuery = true)
    public List<Farm> findJobByPlantId(String batch);

    List<Farm> getByBatch(String batch);


    @Query(value = "select * from t_plant_farm where end_time between ?1 and ?2", nativeQuery = true)
    List<Farm> getByTime(String startTime, String endTime);


    /**
     * 管理员查询
     *
     * @param plantId
     * @return
     */
    @Query(value = "select * from t_plant_farm where plant_id = ?1 and status!=1 order by begin_time asc", nativeQuery = true)
    public List<Farm> findJobDetailByPlantId(String plantId);

    @Query(value = "select * from t_plant_farm where batch = ?1 and (job_person_id=?2  or (principal_id=?2 and status=1)) order by begin_time asc", nativeQuery = true)
    public List<Farm> findJobDetail(String batch, String userId);

    public List<Farm> findByPid(String pid);

    List<Farm> findByPlantId(String plantId);

    @Query(value = "select count(id) from t_plant_farm where company_id=?1", nativeQuery = true)
    int fieldCZCount(String companyId);

    @Query(value = "select * from t_plant_farm where company_id=?1", nativeQuery = true)
    List<Farm> getByCompanyId(String companyId);
}
